На данный момент, все процедуры, мы создавали, имеют тип Public , что означает, что они доступны из любого модуля.
Sub example() 'Идентична к: Public Sub example()
Чтобы сделать процедуру доступной только в определенном модуле, используется ключевое слово Private:
Private Sub example()
Чтобы выполнить процедуру с середины другой процедуры, просто введите ее название.
Здесь есть очень простой пример:
Private Sub warning()
MsgBox "Caution !!!"
End Sub
Sub macro_test()
If Range("A1") = "" Then
warning ' <= выполнить процедуру "warning"
End If
'и т.д.
End Sub
Аргументы делают возможным использование значений из процедуры в под-процедуры (запомните, что по умолчанию, переменные являются доступны только по той процедуры, в которой они были объявлены).
Private Sub warning(var_text As String)
MsgBox "Caution : " & var_text & " !"
End Sub
Sub macro_test()
If Range("A1") = "" Then
warning "пустая ячейка"
ElseIf Not IsNumeric(Range("A1")) Then
warning "нецифровое значение"
End If
End Sub
К процедуре "warning" был добавлен аргумент, в данном случае это переменная "var_text" с типом "String" (строка):
Private Sub warning(var_text As String)
Эта процедура требует аргумент, поэтому мы должны поставить значение после "warning", чтобы выполнить ее:
warning "пустая ячейка"
Когда мы хотим прописать несколько аргументов, тогда они должны быть отделены запятыми.
По умолчанию, если процедура имеет аргументы, то они должны быть обязательно проставлены, и если они не проставлены, тогда процедура не выполнится.
Необязательный аргумент может быть добавлен после обязательного, с помощью ключевого слова Optional . Например:
Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)
Теперь эта процедура может быть выполнена с или без опционального аргумента, как здесь:
'Пример 1: отображаем фамилию: dialog_boxes last_name1 'Пример 2: отображаем фамилию и имя: dialog_boxes last_name1, first_name1 'Пример 3: отображаем фамилию и возраст: dialog_boxes last_name1, , age1 'Пример 4: отображаем фамилию, имя и возраст: dialog_boxes last_name1, first_name1, age1
Аргументы должны быть введены в правильном порядке.
Чтобы протестировать, присутствует ли опциональный аргумент в процедуре, мы используем функцию IsMissing . Эта функция совместима только с некоторыми типами функций (типа Variant) и это является решающим, так как тип необязательно аргументов не был указан в объявлении (необъявленный тип = Variant).
Здесь есть пример, который использует два фрагмента кода, которые рассматривались выше:
Sub macro_test()
Dim last_name1 As String, first_name1 As String, age1 As Integer
last_name1 = Range("A1")
first_name1 = Range("B1")
age1 = Range("C1")
'Пример 1: отображаем фамилию:
dialog_boxes last_name1
'Пример 2: отображаем фамилию и имя:
dialog_boxes last_name1, first_name1
'Пример 3: отображаем фамилию и возраст:
dialog_boxes last_name1, , age1
'Пример 4: отображаем фамилию, имя и возраст:
dialog_boxes last_name1, first_name1, age1
End Sub
Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)
If IsMissing(age) Then 'Если переменная age отсутствует...
If IsMissing(first_name) Then 'Если переменная first_name отсутствует, тогда
'будет отображаться только фамилия
MsgBox last_name
Else 'В противном случае, будет отображаться фамилия и имя
MsgBox last_name & " " & first_name
End If
Else 'Если переменная age присутствует...
If IsMissing(first_name) Then 'Если переменная first_name отсутствует, тогда
'будет отображено фамилию и возраст
MsgBox last_name & ", " & age & " лет"
Else 'В противном случае будет отображено фамилию, имя и возраст
MsgBox last_name & " " & first_name & ", " & age & " лет"
End If
End If
End Sub
См. рисунок ниже (пример 1):
По умолчанию, аргументы имеют тип ByRef , что означает: если переменная передается как аргумент, ссылка на нее будет также передаваться. Иными словами, если переменная была изменена другой под-процедурой, то она также будет изменена во внешней процедуре, которая вызывает эту под-процедуру.
Наприклад:
Sub macro_test()
Dim var_number As Integer
var_number = 30
calcul_square var_number
MsgBox var_number
End Sub
Private Sub calcul_square(ByRef var_value As Integer) 'ByRef не обязательно указывать
'(является значением по умолчанию)
var_value = var_value * var_value
End Sub
Чтобы стало понятнее, ниже есть пример того, что произойдет, если макрос будет запущен на выполнение:
var_number = 30 'Начальное значение переменной "var_number" є 30 calcul_square var_number 'Под-процедура запускается с "var_number" как аргумент Private Sub calcul_square(ByRef var_value As Integer) 'Переменная "var_value" в некоторой степени служит для быстрого доступа к переменной "var_number", 'что означает, что если переменная "var_value" изменена, переменная "var_number" будет также изменена "(и они не должны обязательно иметь одинаковое имя) var_value = var_value * var_value 'Значение переменной "var_value" изменено (и поэтому "var_number" также одновременно изменено) End Sub 'Конец под-процедуры MsgBox var_number 'Переменная "var_number" была изменена, поэтому 900 будет сейчас отображено в диалоговом окнеВторой метод заключается в использовании ByVal .
В отличие от ByRef , который передает ссылки (ярлык), ByVal передает значение, которое означает, что значение передано как аргумент не было изменено.
Ниже вы можете увидеть как предыдущий код и ByVal работают:
var_number = 30 'Начальное значение переменной "var_number" есть 30 calcul_square var_number 'Под-процедура запускается с "var_number" как аргумент Private Sub calcul_square (ByVal var_value As Integer) 'Переменная "var_value" копирует значение переменной "var_number" (2-е переменные не являются связанными) var_value = var_value * var_value 'Значение переменной "var_value" изменено End Sub 'Конец под-процедуры (в этом примере под-процедура не имеет никакого влияния ни на что) MsgBox var_number 'Переменная "var_number" не была изменена, и поэтому 30 будет отображено в диалоговом окне
Что вам нужно запомнить: используйте ByVal когда переменная не должна быть изменена ...
Основным отличием между процедурой и функцией является то, что функция возвращает значение.
Вот простой пример:
Function square(var_number)
square = var_number ^ 2 'Функция "square" возвращает значение "корень квадратный"
End Function
Sub macro_test()
Dim result As Double
result = square(9.876) 'Переменной result присваивается значение, которое было рассчитано функцией
MsgBox result 'Отображается результат (в данном случае квадрат для 9.876)
End Sub
Функция может быть использована на рабочем листе, подобно любой другой функции в Excel.
Например, чтобы получить квадрат значения, которое введенное в ячейку A1:
